"""
cron: 0 */4 * * *
new Env('电商热销榜分析专业版');
"""
import requests
import os
import mysql.connector
import json
import time
from datetime import datetime
from bs4 import BeautifulSoup

class HotProductsPro:
    def __init__(self):
        self.connection = mysql.connector.connect(
            host=os.environ.get('HOST'),
            user=os.environ.get('USER'),
            password=os.environ.get('PASSWORD'),
            database=os.environ.get('DATABASE')
        )
        self.cursor = self.connection.cursor()
        self.create_tables()
        
    def create_tables(self):
        """创建必要的数据表"""
        # 热销商品表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_hot_products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                product_id VARCHAR(100),
                title VARCHAR(500),
                platform VARCHAR(50),
                category VARCHAR(100),
                price DECIMAL(10,2),
                sales_count INT,
                rating DECIMAL(3,1),
                shop_name VARCHAR(200),
                features TEXT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_platform (platform),
                INDEX idx_category (category),
                INDEX idx_create_time (create_time)
            )
        """)
        
        # 类目分析表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_category_analysis (
                id INT AUTO_INCREMENT PRIMARY KEY,
                platform VARCHAR(50),
                category VARCHAR(100),
                avg_price DECIMAL(10,2),
                total_sales INT,
                growth_rate DECIMAL(5,2),
                top_brands TEXT,
                price_distribution TEXT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_platform (platform),
                INDEX idx_category (category)
            )
        """)
        
        # 店铺分析表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_shop_analysis (
                id INT AUTO_INCREMENT PRIMARY KEY,
                platform VARCHAR(50),
                shop_name VARCHAR(200),
                total_sales INT,
                avg_rating DECIMAL(3,1),
                main_categories TEXT,
                price_level INT,
                competitive_power INT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_platform (platform),
                INDEX idx_shop_name (shop_name)
            )
        """)

    def get_jd_hot_products(self, category):
        """获取京东热销商品数据"""
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
                'Referer': 'https://www.jd.com/'
            }
            
            url = f'https://list.jd.com/list.html?cat={category}&page=1&sort=sort_totalsales15_desc'
            response = requests.get(url, headers=headers)
            
            if response.status_code == 200:
                soup = BeautifulSoup(response.text, 'html.parser')
                products = soup.find_all('li', class_='gl-item')
                
                for product in products[:50]:  # 获取前50个商品
                    product_id = product['data-sku']
                    title = product.find('div', class_='p-name').text.strip()
                    price = float(product.find('div', class_='p-price').strong.i.text)
                    shop = product.find('div', class_='p-shop').text.strip()
                    
                    # 获取评分和销量
                    detail_url = f'https://item.jd.com/{product_id}.html'
                    detail_response = requests.get(detail_url, headers=headers)
                    detail_soup = BeautifulSoup(detail_response.text, 'html.parser')
                    
                    rating = float(detail_soup.find('div', class_='rating').text) if detail_soup.find('div', class_='rating') else 0
                    sales = int(detail_soup.find('div', class_='count').text.replace('人已评价', '')) if detail_soup.find('div', class_='count') else 0
                    
                    # 提取商品特征
                    features = [tag.text for tag in detail_soup.find_all('li', class_='parameter2')]
                    
                    product_data = {
                        'product_id': product_id,
                        'title': title,
                        'platform': 'jd',
                        'category': category,
                        'price': price,
                        'sales_count': sales,
                        'rating': rating,
                        'shop_name': shop,
                        'features': json.dumps(features, ensure_ascii=False)
                    }
                    
                    self.save_product_data(product_data)
                    time.sleep(1)  # 避免请求过快
                    
        except Exception as e:
            print(f"获取京东热销商品失败: {str(e)}")

    def get_tmall_hot_products(self, category):
        """获取天猫热销商品数据"""
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
                'Cookie': os.environ.get('TMALL_COOKIE', '')  # 需要登录cookie
            }
            
            url = f'https://list.tmall.com/search_product.htm?cat={category}&sort=d'
            response = requests.get(url, headers=headers)
            
            if response.status_code == 200:
                soup = BeautifulSoup(response.text, 'html.parser')
                products = soup.find_all('div', class_='product')
                
                for product in products[:50]:  # 获取前50个商品
                    product_id = product['data-id']
                    title = product.find('p', class_='productTitle').text.strip()
                    price = float(product.find('p', class_='productPrice').em.text)
                    shop = product.find('a', class_='productShop').text.strip()
                    
                    # 获取评分和销量
                    detail_url = f'https://detail.tmall.com/item.htm?id={product_id}'
                    detail_response = requests.get(detail_url, headers=headers)
                    detail_soup = BeautifulSoup(detail_response.text, 'html.parser')
                    
                    rating = float(detail_soup.find('span', class_='tm-rate').text) if detail_soup.find('span', class_='tm-rate') else 0
                    sales = int(detail_soup.find('span', class_='tm-count').text) if detail_soup.find('span', class_='tm-count') else 0
                    
                    # 提取商品特征
                    features = [li.text for li in detail_soup.find_all('li', class_='tm-property')]
                    
                    product_data = {
                        'product_id': product_id,
                        'title': title,
                        'platform': 'tmall',
                        'category': category,
                        'price': price,
                        'sales_count': sales,
                        'rating': rating,
                        'shop_name': shop,
                        'features': json.dumps(features, ensure_ascii=False)
                    }
                    
                    self.save_product_data(product_data)
                    time.sleep(1)  # 避免请求过快
                    
        except Exception as e:
            print(f"获取天猫热销商品失败: {str(e)}")

    def analyze_category(self, platform, category):
        """分析类目数据"""
        try:
            # 获取类目基础数据
            query = """
                SELECT AVG(price) as avg_price,
                       SUM(sales_count) as total_sales,
                       GROUP_CONCAT(DISTINCT shop_name) as shops
                FROM ningm_hot_products
                WHERE platform = %s AND category = %s
                AND create_time > DATE_SUB(NOW(), INTERVAL 7 DAY)
            """
            self.cursor.execute(query, (platform, category))
            result = self.cursor.fetchone()
            
            if not result:
                return
                
            avg_price, total_sales, shops = result
            
            # 计算增长率
            prev_query = """
                SELECT SUM(sales_count) as prev_sales
                FROM ningm_hot_products
                WHERE platform = %s AND category = %s
                AND create_time BETWEEN DATE_SUB(NOW(), INTERVAL 14 DAY) AND DATE_SUB(NOW(), INTERVAL 7 DAY)
            """
            self.cursor.execute(prev_query, (platform, category))
            prev_sales = self.cursor.fetchone()[0] or 0
            
            growth_rate = ((total_sales - prev_sales) / prev_sales * 100) if prev_sales > 0 else 0
            
            # 获取价格分布
            price_query = """
                SELECT 
                    CASE 
                        WHEN price <= 100 THEN '0-100'
                        WHEN price <= 500 THEN '100-500'
                        WHEN price <= 1000 THEN '500-1000'
                        ELSE '1000+'
                    END as price_range,
                    COUNT(*) as count
                FROM ningm_hot_products
                WHERE platform = %s AND category = %s
                GROUP BY 
                    CASE 
                        WHEN price <= 100 THEN '0-100'
                        WHEN price <= 500 THEN '100-500'
                        WHEN price <= 1000 THEN '500-1000'
                        ELSE '1000+'
                    END
            """
            self.cursor.execute(price_query, (platform, category))
            price_distribution = dict(self.cursor.fetchall())
            
            # 获取热门品牌
            brand_query = """
                SELECT shop_name, SUM(sales_count) as total_sales
                FROM ningm_hot_products
                WHERE platform = %s AND category = %s
                GROUP BY shop_name
                ORDER BY total_sales DESC
                LIMIT 10
            """
            self.cursor.execute(brand_query, (platform, category))
            top_brands = dict(self.cursor.fetchall())
            
            # 保存分析结果
            insert_query = """
                INSERT INTO ningm_category_analysis 
                (platform, category, avg_price, total_sales, growth_rate, 
                 top_brands, price_distribution) 
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                platform,
                category,
                avg_price,
                total_sales,
                growth_rate,
                json.dumps(top_brands, ensure_ascii=False),
                json.dumps(price_distribution)
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"分析类目数据失败: {str(e)}")
            self.connection.rollback()

    def analyze_shop(self, platform, shop_name):
        """分析店铺数据"""
        try:
            query = """
                SELECT 
                    SUM(sales_count) as total_sales,
                    AVG(rating) as avg_rating,
                    GROUP_CONCAT(DISTINCT category) as categories,
                    AVG(price) as avg_price
                FROM ningm_hot_products
                WHERE platform = %s AND shop_name = %s
            """
            self.cursor.execute(query, (platform, shop_name))
            result = self.cursor.fetchone()
            
            if not result:
                return
                
            total_sales, avg_rating, categories, avg_price = result
            
            # 计算价格水平（1-5）
            price_level = self.calculate_price_level(avg_price)
            
            # 计算竞争力（1-100）
            competitive_power = self.calculate_competitive_power(
                total_sales,
                avg_rating,
                len(categories.split(',')) if categories else 0,
                price_level
            )
            
            # 保存分析结果
            insert_query = """
                INSERT INTO ningm_shop_analysis 
                (platform, shop_name, total_sales, avg_rating, main_categories, 
                 price_level, competitive_power) 
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                platform,
                shop_name,
                total_sales,
                avg_rating,
                categories,
                price_level,
                competitive_power
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"分析店铺数据失败: {str(e)}")
            self.connection.rollback()

    def calculate_price_level(self, avg_price):
        """计算价格水平"""
        if avg_price <= 50:
            return 1
        elif avg_price <= 200:
            return 2
        elif avg_price <= 500:
            return 3
        elif avg_price <= 1000:
            return 4
        else:
            return 5

    def calculate_competitive_power(self, sales, rating, category_count, price_level):
        """计算店铺竞争力"""
        try:
            # 销量得分（最高40分）
            if sales > 10000:
                sales_score = 40
            elif sales > 5000:
                sales_score = 30
            elif sales > 1000:
                sales_score = 20
            else:
                sales_score = 10
            
            # 评分得分（最高30分）
            rating_score = min(rating * 6, 30)
            
            # 类目覆盖得分（最高20分）
            category_score = min(category_count * 4, 20)
            
            # 价格水平得分（最高10分）
            price_score = (6 - price_level) * 2
            
            return min(sales_score + rating_score + category_score + price_score, 100)
            
        except Exception as e:
            print(f"计算竞争力失败: {str(e)}")
            return 0

    def save_product_data(self, product_data):
        """保存商品数据"""
        try:
            insert_query = """
                INSERT INTO ningm_hot_products 
                (product_id, title, platform, category, price, sales_count, 
                 rating, shop_name, features) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                product_data['product_id'],
                product_data['title'],
                product_data['platform'],
                product_data['category'],
                product_data['price'],
                product_data['sales_count'],
                product_data['rating'],
                product_data['shop_name'],
                product_data['features']
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"保存商品数据失败: {str(e)}")
            self.connection.rollback()

    def run(self):
        """运行分析系统"""
        try:
            # 热门类目列表
            categories = {
                'jd': ['9987,653,655', '670,671,672', '1315,1342,1343'],  # 手机、电脑、数码
                'tmall': ['50011740', '50011699', '50008090']  # 对应天猫类目
            }
            
            # 1. 获取各平台热销商品
            for platform, cats in categories.items():
                for category in cats:
                    if platform == 'jd':
                        self.get_jd_hot_products(category)
                    elif platform == 'tmall':
                        self.get_tmall_hot_products(category)
                    time.sleep(5)
            
            # 2. 分析类目数据
            for platform, cats in categories.items():
                for category in cats:
                    self.analyze_category(platform, category)
            
            # 3. 分析店铺数据
            self.cursor.execute("SELECT DISTINCT platform, shop_name FROM ningm_hot_products")
            shops = self.cursor.fetchall()
            
            for platform, shop_name in shops:
                self.analyze_shop(platform, shop_name)
                time.sleep(1)
            
        except Exception as e:
            print(f"系统运行失败: {str(e)}")
        finally:
            self.cursor.close()
            self.connection.close()

if __name__ == "__main__":
    analyzer = HotProductsPro()
    analyzer.run() 